﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace iTool.Cloud.Center.ServiceProvider.StorageProvider
{
    internal class iSqliteProvide
    {
        static string connectionOfString;
        static iSqliteProvide()
        {
            connectionOfString = "Data Source=./iToolCloudCenter.storage";

            List<string> sqls = new List<string>
            {
                @"
                        CREATE TABLE IF NOT EXISTS KeyValues(
                            [Cluster] NVARCHAR(200) NOT NULL,
                            [Type] NVARCHAR(50) NOT NULL,
                            [Value] TEXT NULL
                        )",
                @"
                        CREATE TABLE IF NOT EXISTS Loggers(
                            [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            [Type] NVARCHAR(50) NOT NULL,
                            [Value] TEXT NULL,
                            [CreateDate] NUMERIC DEFAULT (datetime('now','localtime'))
                        )"
            };
            using (var connection = new SqliteConnection(connectionOfString))
            {
                connection.Open();
                SqliteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                foreach (var sql in sqls)
                {
                    using (SqliteCommand sqliteCommand = new SqliteCommand(sql, connection, transaction))
                    {
                        sqliteCommand.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }


        }

        public static int ExecuteNonQuery(string sql, SqliteTransaction? transaction = null, params SqliteParameter[] parameters)
        {
            using (var connection = new SqliteConnection(connectionOfString))
            {
                using (SqliteCommand sqliteCommand = CreateCommand(sql, connection, transaction, parameters: parameters))
                {
                    return sqliteCommand.ExecuteNonQuery();
                }
            }
        }

        public static int ExecuteNonQuery(string sql, SqliteConnection connection, SqliteTransaction? transaction = null, params SqliteParameter[] parameters)
        {
            using (SqliteCommand sqliteCommand = CreateCommand(sql, connection, transaction, parameters: parameters))
            {
                return sqliteCommand.ExecuteNonQuery();
            }
        }

        public static void BatchWithTransaction(List<string> sqls)
        {
            using (var connection = new SqliteConnection(connectionOfString))
            {
                connection.Open();
                SqliteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                try
                {
                    foreach (string sql in sqls)
                    {
                        ExecuteNonQuery(sql, connection, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                }
                finally
                {
                    transaction.Dispose();
                }
            }
        }

        public static T ExecuteQuery<T>(string sql, params SqliteParameter[] parameters)
            where T : class, new()
        {
            T result = new T();
            PropertyInfo[] propertyInfos = result.GetType().GetProperties();
            using (var connection = new SqliteConnection(connectionOfString))
            {
                using (SqliteCommand sqliteCommand = CreateCommand(sql, connection, parameters: parameters))
                {
                    var reader = sqliteCommand.ExecuteReader();
                    if (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            string columnName = reader.GetName(i);
                            object columnValue = reader.GetValue(i);
                            PropertyInfo propertyInfo = propertyInfos.Where(property => property.Name == columnName).FirstOrDefault();
                            if (propertyInfo != null && columnValue != DBNull.Value)
                            {
                                propertyInfo.SetValue(result, columnValue, null);
                            }
                        }
                    }

                    return result;
                }
            }
        }

        public static DataTable ExecuteQueryOfTable(string sql, params SqliteParameter[] parameters)
        {
            using (var connection = new SqliteConnection(connectionOfString))
            {
                using (SqliteCommand sqliteCommand = CreateCommand(sql, connection, parameters: parameters))
                {
                    var result = sqliteCommand.ExecuteReader();
                    return ConvertDataReaderToDataTable(result);
                }
            }
        }

        static DataTable ConvertDataReaderToDataTable(SqliteDataReader reader)
        {
            try
            {
                DataTable objDataTable = new DataTable();
                int intFieldCount = reader.FieldCount;
                for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                }
                objDataTable.BeginLoadData();

                object[] objValues = new object[intFieldCount];
                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();
                objDataTable.EndLoadData();

                return objDataTable;

            }
            catch (Exception ex)
            {
                throw new Exception("转换出错!", ex);
            }
        }

        static SqliteCommand CreateCommand(string sql, SqliteConnection connection, SqliteTransaction? transaction = null, params SqliteParameter[] parameters)
        {
            connection.Open();
            var command = new SqliteCommand(sql, connection, transaction);
            command.Parameters.AddRange(parameters);
            return command;
        }
    }

}
